﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using KaoWuYouLibs.Common;
using KaoWuYouLibs.Model;

namespace KaoWuYouLibs.Dal
{
    public class WebUserAccess
    {
        public bool Register(string AccountName, string EmailAddress, string Password, string Name, string Gender, string Tel)
        {
            SqlConnection conn = new SqlConnection(Tools.ConnString);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = @"INSERT INTO Users(AccountName,EmailAddress,Password,Name,Authority,Gender,Tel,LastDate) 
                 VALUES(@AccountName,@EmailAddress,@Password,@Name,@Authority,@Gender,@Tel,@LastDate)  ";
                cmd.Parameters.AddRange(new SqlParameter[]
                    {
                        new SqlParameter("@AccountName",AccountName),
                        new SqlParameter("@EmailAddress",EmailAddress),
                        new SqlParameter("@Password",Password),
                        new SqlParameter("@Name",Name),
                        new SqlParameter("@Authority",100),
                        new SqlParameter("@Gender",Gender),
                        new SqlParameter("@Tel",Tel),
                        new SqlParameter("@LastDate",DateTime.Now)
                    });
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                conn.Close();
            }
        }

        public bool UpdatePassword(string Password, string AccountName)
        {
            SqlConnection conn = new SqlConnection(Tools.ConnString);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = "UPDATE Users SET Password = @Password WHERE AccountName = @AccountName";
                cmd.Parameters.AddRange(new SqlParameter[]
                    {
                        new SqlParameter("@Password",Password),
                        new SqlParameter("@AccountName",AccountName)
                    });
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                conn.Close();
            }
        }

        public bool UpdateUserInfo(string AccountName, string EmailAddress, string Name, string Gender, string Tel)
        {
            SqlConnection conn = new SqlConnection(Tools.ConnString);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = "UPDATE Users SET (EmailAddress = @EmailAddress,Name = @Name,Gender = @Gender,Tel = @Tel) WHERE AccountName = @AccountName";
                cmd.Parameters.AddRange(new SqlParameter[]
                    {
                        new SqlParameter("@EmailAddress",EmailAddress),
                        new SqlParameter("@Name",Name),
                        new SqlParameter("@Gender",Gender),
                        new SqlParameter("@Tel",Tel),
                        new SqlParameter("@AccountName", AccountName)
                    });
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                conn.Close();
            }
        }

        public bool DeleteWebUser(string AccountName)
        {
            SqlConnection conn = new SqlConnection(Tools.ConnString);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = @"DELETE FROM Users WHERE AccountName = @AccountName";
                cmd.Parameters.AddRange(new SqlParameter[]
                    {
                        new SqlParameter("@AccoutName",AccountName)
                    });
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                conn.Close();
            }
        }

        public List<WebUserModel> GetPassword(string AccountName, string Password)
        {
            SqlConnection conn = new SqlConnection(Tools.ConnString);
            try
            {
                conn.Open();
                List<WebUserModel> re = new List<WebUserModel>();

                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT Password FROM Users WHERE AccountName = @AccountName";
                cmd.Parameters.AddRange(new SqlParameter[] 
            { 
                new SqlParameter("@AccountName", AccountName),
                new SqlParameter("@Password", Password),
            });
                SqlDataReader sdr = cmd.ExecuteReader();
                while (sdr.Read())
                {
                    WebUserModel tmp = new WebUserModel()
                    {
                        AccountName = sdr["AccountName"] as string,
                        Password = sdr["Password"] as string
                    };
                    re.Add(tmp);
                }

                sdr.Close();
                return re;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                conn.Close();
            }
        }

        public bool FindUserByAccountNameAndPassword(string AccountName, string Password)
        {
            SqlConnection conn = new SqlConnection(Tools.ConnString);
            try
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = "SELECT * FROM Users WHERE (AccountName=@AccountName)AND(Password=@Password)";
                cmd.Parameters.AddRange(new SqlParameter[]
                    {                        
                        new SqlParameter("@AccountName", AccountName),
                        new SqlParameter("@Password", Password)
                    });

                if (cmd.ExecuteScalar() == null)
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                conn.Close();
            }
        }

        public WebUserModel FindUserInformation(string AccountName, string Password)
        {
            SqlConnection conn = new SqlConnection(Tools.ConnString);
            try
            {
                conn.Open();
                WebUserModel re = null;
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM Users WHERE (AccountName=@AccountName)AND(Password=@Password)";
                cmd.Parameters.AddRange(new SqlParameter[]
                    {                        
                        new SqlParameter("@AccountName", AccountName),
                        new SqlParameter("@Password", Password)
                    });
                SqlDataReader sdr = cmd.ExecuteReader();
                if (sdr.Read())
                {
                    re = new WebUserModel()
                    {
                        AccountName = sdr["AccountName"] as string,
                        Authority = (byte)sdr["Authority"],
                        EmailAddress = sdr["EmailAddress"] as string,
                        Gender = sdr["Gender"] as string,
                        LastLoginDate = (DateTime)sdr["LastDate"],
                        Name = sdr["Name"] as string,
                        Tel = sdr["Tel"] as string
                    };
                }
                return re;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                conn.Close();
            }
        }

        public List<WebUserModel> UserQueryByLastLoginDate(DateTime LastLoginDate)
        {

            SqlConnection conn = new SqlConnection(Tools.ConnString);
            try
            {
                conn.Open();
                List<WebUserModel> re = new List<WebUserModel>();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM Users WHERE LastLoginDate >= @LastLoginDate";
                cmd.Parameters.AddRange(new SqlParameter[] 
            { 
                new SqlParameter("@LastLoginDate", LastLoginDate) 
            });
                SqlDataReader sdr = cmd.ExecuteReader();
                while (sdr.Read())
                {
                    WebUserModel tmp = new WebUserModel()
                    {
                        AccountName = sdr["AccountName"] as string ,
                        LastLoginDate = (DateTime)sdr["LastLoginDate"]
                    };
                    re.Add(tmp);
                }

                sdr.Close();
                return re;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                conn.Close();
            }
        }
    }
}
